{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6ff90216-debd-49cc-8995-957856da87ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "5f76cc71-1224-4b0a-99f6-1021f19ea69f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df0 = pd.read_csv(r'数据\\county_values_2021_xiaomai.csv',header = 0)\n",
    "df1 = pd.read_csv(r'数据\\county_values_2020_xiaomai.csv',header = 0)\n",
    "df2 = pd.read_csv(r'数据\\county_values_2019_xiaomai.csv',header = 0)\n",
    "df3 = pd.read_csv(r'数据\\county_values_2018_xiaomai.csv',header = 0)\n",
    "df4 = pd.read_csv(r'数据\\county_values_2017_xiaomai.csv',header = 0)\n",
    "df5 = pd.read_csv(r'数据\\county_values_2016_xiaomai.csv',header = 0)\n",
    "df_muchan = pd.read_excel(r'数据\\henan_production_all.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "8c1deda8-3256-4f1d-8a13-78837340cbc2",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df0['system:index'] = df0['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "#修改后的列替换为只显示日期\n",
    "df0['system:index'] = df0['system:index'].str.replace('_', '',2)\n",
    "df1['system:index'] = df1['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "df1['system:index'] = df1['system:index'].str.replace('_', '',2)\n",
    "df2['system:index'] = df2['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "df2['system:index'] = df2['system:index'].str.replace('_', '',2)\n",
    "df3['system:index'] = df3['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "df3['system:index'] = df3['system:index'].str.replace('_', '',2)\n",
    "df4['system:index'] = df4['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "df4['system:index'] = df4['system:index'].str.replace('_', '',2)\n",
    "df5['system:index'] = df5['system:index'].str.extract(r'(\\d{4}_\\d{2}_\\d{2}.*)')\n",
    "df5['system:index'] = df5['system:index'].str.replace('_', '',2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "ed3ba948-bca3-40f4-970a-514ae2f25a94",
   "metadata": {},
   "outputs": [],
   "source": [
    "df0['date'] = df0['system:index'].str.split('_').str[0]  #.str[4:]  # 提取月份和日子部分\n",
    "# 将日期转换为日期时间格式\n",
    "df0['date'] = pd.to_datetime(df0['date'], format='%Y%m%d')\n",
    "df1['date'] = df1['system:index'].str.split('_').str[0]  \n",
    "df1['date'] = pd.to_datetime(df1['date'], format='%Y%m%d')\n",
    "df2['date'] = df2['system:index'].str.split('_').str[0]  \n",
    "df2['date'] = pd.to_datetime(df2['date'], format='%Y%m%d')\n",
    "df3['date'] = df3['system:index'].str.split('_').str[0]  \n",
    "df3['date'] = pd.to_datetime(df3['date'], format='%Y%m%d')\n",
    "df4['date'] = df4['system:index'].str.split('_').str[0]  \n",
    "df4['date'] = pd.to_datetime(df4['date'], format='%Y%m%d')\n",
    "df5['date'] = df5['system:index'].str.split('_').str[0] \n",
    "df5['date'] = pd.to_datetime(df5['date'], format='%Y%m%d')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c43efffa-f8cb-4b11-b5b5-b52cf2bad2ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "df0=df0.drop(columns = [\".geo\",'system:index','Unnamed: 33'])\n",
    "df1=df1.drop(columns = [\".geo\",'system:index','Unnamed: 33'])\n",
    "df2=df2.drop(columns = [\".geo\",'system:index','Unnamed: 33'])\n",
    "df3=df3.drop(columns = [\".geo\",'system:index','Unnamed: 33'])\n",
    "df4=df4.drop(columns = [\".geo\",'system:index','Unnamed: 33'])\n",
    "df5=df5.drop(columns = [\".geo\",'system:index','Unnamed: 33'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1df47335-15ea-451b-95c1-9a982b896541",
   "metadata": {},
   "source": [
    "### 2021年指标处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "5935deff-0615-4c32-91d4-ec5a3e134683",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1280)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2021=df0.set_index('NAME') # 设置索引列\n",
    "zhibiao_2021 = pd.DataFrame()\n",
    "zhibiao=list(df2021.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2021['1008'+i]=df2021[df2021['date']=='2021/10/8'][i]\n",
    "    zhibiao_2021['1016'+i]=df2021[df2021['date']=='2021/10/16'][i]\n",
    "    zhibiao_2021['1024'+i]=df2021[df2021['date']=='2021/10/24'][i]\n",
    "    zhibiao_2021['1101'+i]=df2021[df2021['date']=='2021/11/01'][i]\n",
    "    zhibiao_2021['1109'+i]=df2021[df2021['date']=='2021/11/09'][i]\n",
    "    zhibiao_2021['1117'+i]=df2021[df2021['date']=='2021/11/17'][i]\n",
    "    zhibiao_2021['1125'+i]=df2021[df2021['date']=='2021/11/25'][i]\n",
    "    zhibiao_2021['1203'+i]=df2021[df2021['date']=='2021/12/03'][i]\n",
    "    zhibiao_2021['1211'+i]=df2021[df2021['date']=='2021/12/11'][i]\n",
    "    zhibiao_2021['1219'+i]=df2021[df2021['date']=='2021/12/19'][i]\n",
    "    zhibiao_2021['1227'+i]=df2021[df2021['date']=='2021/12/27'][i]\n",
    "    zhibiao_2021['0101'+i]=df2021[df2021['date']=='2022/01/01'][i]\n",
    "    zhibiao_2021['0109'+i]=df2021[df2021['date']=='2022/01/09'][i]\n",
    "    zhibiao_2021['0117'+i]=df2021[df2021['date']=='2022/01/17'][i]\n",
    "    zhibiao_2021['0125'+i]=df2021[df2021['date']=='2022/01/25'][i]\n",
    "    zhibiao_2021['0202'+i]=df2021[df2021['date']=='2022/02/02'][i]   \n",
    "    zhibiao_2021['0210'+i]=df2021[df2021['date']=='2022/02/10'][i]\n",
    "    zhibiao_2021['0218'+i]=df2021[df2021['date']=='2022/02/18'][i]\n",
    "    zhibiao_2021['0226'+i]=df2021[df2021['date']=='2022/02/26'][i]\n",
    "    zhibiao_2021['0306'+i]=df2021[df2021['date']=='2022/03/06'][i]\n",
    "    zhibiao_2021['0314'+i]=df2021[df2021['date']=='2022/03/14'][i]\n",
    "    zhibiao_2021['0322'+i]=df2021[df2021['date']=='2022/03/22'][i]\n",
    "    zhibiao_2021['0330'+i]=df2021[df2021['date']=='2022/03/30'][i]\n",
    "    zhibiao_2021['0407'+i]=df2021[df2021['date']=='2022/04/07'][i]\n",
    "    zhibiao_2021['0415'+i]=df2021[df2021['date']=='2022/04/15'][i]\n",
    "    zhibiao_2021['0423'+i]=df2021[df2021['date']=='2022/04/23'][i]\n",
    "    zhibiao_2021['0501'+i]=df2021[df2021['date']=='2022/05/01'][i]\n",
    "    zhibiao_2021['0509'+i]=df2021[df2021['date']=='2022/05/09'][i]\n",
    "    zhibiao_2021['0517'+i]=df2021[df2021['date']=='2022/05/17'][i]\n",
    "    zhibiao_2021['0525'+i]=df2021[df2021['date']=='2022/05/25'][i]\n",
    "    zhibiao_2021['0602'+i]=df2021[df2021['date']=='2022/06/02'][i]\n",
    "    zhibiao_2021['0610'+i]=df2021[df2021['date']=='2022/06/10'][i]\n",
    "zhibiao_2021.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "bfb6cd2c-68a3-4b4e-9e11-1b2f491144d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2021.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37b075bd-1c95-4b02-8e1c-01afca4f9e59",
   "metadata": {},
   "source": [
    "### 2020年数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "5993b906-ca7b-4e59-a006-753260cbcee6",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1248)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2020=df1.set_index('NAME') # 设置索引列\n",
    "zhibiao_2020 = pd.DataFrame()\n",
    "zhibiao=list(df2020.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2020['1007'+i]=df2020[df2020['date']=='2020/10/7'][i]\n",
    "    zhibiao_2020['1015'+i]=df2020[df2020['date']=='2020/10/15'][i]\n",
    "    zhibiao_2020['1023'+i]=df2020[df2020['date']=='2020/10/23'][i]\n",
    "    zhibiao_2020['1031'+i]=df2020[df2020['date']=='2020/10/31'][i]\n",
    "    zhibiao_2020['1108'+i]=df2020[df2020['date']=='2020/11/08'][i]\n",
    "    zhibiao_2020['1116'+i]=df2020[df2020['date']=='2020/11/16'][i]\n",
    "    zhibiao_2020['1124'+i]=df2020[df2020['date']=='2020/11/24'][i]\n",
    "    zhibiao_2020['1202'+i]=df2020[df2020['date']=='2020/12/02'][i]\n",
    "    zhibiao_2020['1210'+i]=df2020[df2020['date']=='2020/12/10'][i]\n",
    "    zhibiao_2020['1218'+i]=df2020[df2020['date']=='2020/12/18'][i]\n",
    "    zhibiao_2020['1226'+i]=df2020[df2020['date']=='2020/12/26'][i]\n",
    "    zhibiao_2020['0101'+i]=df2020[df2020['date']=='2021/01/01'][i]\n",
    "    zhibiao_2020['0109'+i]=df2020[df2020['date']=='2021/01/09'][i]\n",
    "    zhibiao_2020['0117'+i]=df2020[df2020['date']=='2021/01/17'][i]\n",
    "    zhibiao_2020['0125'+i]=df2020[df2020['date']=='2021/01/25'][i]\n",
    "    zhibiao_2020['0202'+i]=df2020[df2020['date']=='2021/02/02'][i]   \n",
    "    zhibiao_2020['0210'+i]=df2020[df2020['date']=='2021/02/10'][i]\n",
    "    zhibiao_2020['0218'+i]=df2020[df2020['date']=='2021/02/18'][i]\n",
    "    zhibiao_2020['0226'+i]=df2020[df2020['date']=='2021/02/26'][i]\n",
    "    zhibiao_2020['0306'+i]=df2020[df2020['date']=='2021/03/06'][i]\n",
    "    zhibiao_2020['0314'+i]=df2020[df2020['date']=='2021/03/14'][i]\n",
    "    zhibiao_2020['0322'+i]=df2020[df2020['date']=='2021/03/22'][i]\n",
    "    zhibiao_2020['0330'+i]=df2020[df2020['date']=='2021/03/30'][i]\n",
    "    zhibiao_2020['0407'+i]=df2020[df2020['date']=='2021/04/07'][i]\n",
    "    zhibiao_2020['0415'+i]=df2020[df2020['date']=='2021/04/15'][i]\n",
    "    zhibiao_2020['0423'+i]=df2020[df2020['date']=='2021/04/23'][i]\n",
    "    zhibiao_2020['0501'+i]=df2020[df2020['date']=='2021/05/01'][i]\n",
    "    zhibiao_2020['0509'+i]=df2020[df2020['date']=='2021/05/09'][i]\n",
    "    zhibiao_2020['0517'+i]=df2020[df2020['date']=='2021/05/17'][i]\n",
    "    zhibiao_2020['0525'+i]=df2020[df2020['date']=='2021/05/25'][i]\n",
    "    zhibiao_2020['0602'+i]=df2020[df2020['date']=='2021/06/02'][i]\n",
    "    zhibiao_2020['0610'+i]=df2020[df2020['date']=='2021/06/10'][i]\n",
    "zhibiao_2020.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2f81fa96-ee28-431d-9508-bbe1f70802f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2020.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41297bed-91b7-47a4-99a1-305f703e553b",
   "metadata": {},
   "source": [
    "### 2019年数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fd3f2b3e-66c3-43b4-834f-8ef147dba981",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1248)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2019=df2.set_index('NAME') # 设置索引列\n",
    "zhibiao_2019 = pd.DataFrame()\n",
    "zhibiao=list(df2019.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2019['1008'+i]=df2019[df2019['date']=='2019/10/8'][i]\n",
    "    zhibiao_2019['1016'+i]=df2019[df2019['date']=='2019/10/16'][i]\n",
    "    zhibiao_2019['1024'+i]=df2019[df2019['date']=='2019/10/24'][i]\n",
    "    zhibiao_2019['1101'+i]=df2019[df2019['date']=='2019/11/01'][i]\n",
    "    zhibiao_2019['1109'+i]=df2019[df2019['date']=='2019/11/09'][i]\n",
    "    zhibiao_2019['1117'+i]=df2019[df2019['date']=='2019/11/17'][i]\n",
    "    zhibiao_2019['1125'+i]=df2019[df2019['date']=='2019/11/25'][i]\n",
    "    zhibiao_2019['1203'+i]=df2019[df2019['date']=='2019/12/03'][i]\n",
    "    zhibiao_2019['1211'+i]=df2019[df2019['date']=='2019/12/11'][i]\n",
    "    zhibiao_2019['1219'+i]=df2019[df2019['date']=='2019/12/19'][i]\n",
    "    zhibiao_2019['1227'+i]=df2019[df2019['date']=='2019/12/27'][i]\n",
    "    zhibiao_2019['0101'+i]=df2019[df2019['date']=='2020/01/01'][i]\n",
    "    zhibiao_2019['0109'+i]=df2019[df2019['date']=='2020/01/09'][i]\n",
    "    zhibiao_2019['0117'+i]=df2019[df2019['date']=='2020/01/17'][i]\n",
    "    zhibiao_2019['0125'+i]=df2019[df2019['date']=='2020/01/25'][i]\n",
    "    zhibiao_2019['0202'+i]=df2019[df2019['date']=='2020/02/02'][i]   \n",
    "    zhibiao_2019['0210'+i]=df2019[df2019['date']=='2020/02/10'][i]\n",
    "    zhibiao_2019['0218'+i]=df2019[df2019['date']=='2020/02/18'][i]\n",
    "    zhibiao_2019['0226'+i]=df2019[df2019['date']=='2020/02/26'][i]\n",
    "    zhibiao_2019['0305'+i]=df2019[df2019['date']=='2020/03/05'][i]\n",
    "    zhibiao_2019['0313'+i]=df2019[df2019['date']=='2020/03/13'][i]\n",
    "    zhibiao_2019['0321'+i]=df2019[df2019['date']=='2020/03/21'][i]\n",
    "    zhibiao_2019['0329'+i]=df2019[df2019['date']=='2020/03/29'][i]\n",
    "    zhibiao_2019['0406'+i]=df2019[df2019['date']=='2020/04/06'][i]\n",
    "    zhibiao_2019['0414'+i]=df2019[df2019['date']=='2020/04/14'][i]\n",
    "    zhibiao_2019['0422'+i]=df2019[df2019['date']=='2020/04/22'][i]\n",
    "    zhibiao_2019['0430'+i]=df2019[df2019['date']=='2020/04/30'][i]\n",
    "    zhibiao_2019['0508'+i]=df2019[df2019['date']=='2020/05/08'][i]\n",
    "    zhibiao_2019['0516'+i]=df2019[df2019['date']=='2020/05/16'][i]\n",
    "    zhibiao_2019['0524'+i]=df2019[df2019['date']=='2020/05/24'][i]\n",
    "    zhibiao_2019['0601'+i]=df2019[df2019['date']=='2020/06/01'][i]\n",
    "    zhibiao_2019['0609'+i]=df2019[df2019['date']=='2020/06/09'][i]\n",
    "zhibiao_2019.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "ed22b6bb-9898-41a2-9033-06dfc0fcd81f",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2019.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dad66b82-629c-408d-913f-358fb71646a8",
   "metadata": {},
   "source": [
    "### 2018年数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "a8944f22-b165-4476-945f-4bd2e30bd539",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1209)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2018=df3.set_index('NAME') # 设置索引列\n",
    "zhibiao_2018 = pd.DataFrame()\n",
    "zhibiao=list(df2018.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2018['1008'+i]=df2018[df2018['date']=='2018/10/8'][i]\n",
    "    zhibiao_2018['1016'+i]=df2018[df2018['date']=='2018/10/16'][i]\n",
    "    zhibiao_2018['1024'+i]=df2018[df2018['date']=='2018/10/24'][i]\n",
    "    zhibiao_2018['1101'+i]=df2018[df2018['date']=='2018/11/01'][i]\n",
    "    zhibiao_2018['1109'+i]=df2018[df2018['date']=='2018/11/09'][i]\n",
    "    zhibiao_2018['1117'+i]=df2018[df2018['date']=='2018/11/17'][i]\n",
    "    zhibiao_2018['1125'+i]=df2018[df2018['date']=='2018/11/25'][i]\n",
    "    zhibiao_2018['1203'+i]=df2018[df2018['date']=='2018/12/03'][i]\n",
    "    zhibiao_2018['1211'+i]=df2018[df2018['date']=='2018/12/11'][i]\n",
    "    zhibiao_2018['1219'+i]=df2018[df2018['date']=='2018/12/19'][i]\n",
    "    zhibiao_2018['1227'+i]=df2018[df2018['date']=='2018/12/27'][i]\n",
    "    zhibiao_2018['0101'+i]=df2018[df2018['date']=='2019/01/01'][i]\n",
    "    zhibiao_2018['0109'+i]=df2018[df2018['date']=='2019/01/09'][i]\n",
    "    zhibiao_2018['0117'+i]=df2018[df2018['date']=='2019/01/17'][i]\n",
    "    zhibiao_2018['0125'+i]=df2018[df2018['date']=='2019/01/25'][i]\n",
    "    zhibiao_2018['0202'+i]=df2018[df2018['date']=='2019/02/02'][i]   \n",
    "    zhibiao_2018['0210'+i]=df2018[df2018['date']=='2019/02/10'][i]\n",
    "    zhibiao_2018['0218'+i]=df2018[df2018['date']=='2019/02/18'][i]\n",
    "    zhibiao_2018['0226'+i]=df2018[df2018['date']=='2019/02/26'][i]\n",
    "    zhibiao_2018['0306'+i]=df2018[df2018['date']=='2019/03/06'][i]\n",
    "    zhibiao_2018['0314'+i]=df2018[df2018['date']=='2019/03/14'][i]\n",
    "    zhibiao_2018['0322'+i]=df2018[df2018['date']=='2019/03/22'][i]\n",
    "    zhibiao_2018['0330'+i]=df2018[df2018['date']=='2019/03/30'][i]\n",
    "    zhibiao_2018['0407'+i]=df2018[df2018['date']=='2019/04/07'][i]\n",
    "    zhibiao_2018['0415'+i]=df2018[df2018['date']=='2019/04/15'][i]\n",
    "    zhibiao_2018['0423'+i]=df2018[df2018['date']=='2019/04/23'][i]\n",
    "    zhibiao_2018['0501'+i]=df2018[df2018['date']=='2019/05/01'][i]\n",
    "    zhibiao_2018['0509'+i]=df2018[df2018['date']=='2019/05/09'][i]\n",
    "    zhibiao_2018['0517'+i]=df2018[df2018['date']=='2019/05/17'][i]\n",
    "    zhibiao_2018['0525'+i]=df2018[df2018['date']=='2019/05/25'][i]\n",
    "    zhibiao_2018['0602'+i]=df2018[df2018['date']=='2019/06/02'][i]\n",
    "zhibiao_2018.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "eca1f061-8476-4c85-a946-34afe68acf09",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2018.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6d037253-84f8-442a-9f78-9f18af00d05a",
   "metadata": {},
   "source": [
    "### 2017年数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "96f8846c-4337-4e06-ac22-3722a6479d34",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1209)"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2017=df4.set_index('NAME') # 设置索引列\n",
    "zhibiao_2017= pd.DataFrame()\n",
    "zhibiao=list(df2017.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2017['1008'+i]=df2017[df2017['date']=='2017/10/8'][i]\n",
    "    zhibiao_2017['1016'+i]=df2017[df2017['date']=='2017/10/16'][i]\n",
    "    zhibiao_2017['1024'+i]=df2017[df2017['date']=='2017/10/24'][i]\n",
    "    zhibiao_2017['1101'+i]=df2017[df2017['date']=='2017/11/01'][i]\n",
    "    zhibiao_2017['1109'+i]=df2017[df2017['date']=='2017/11/09'][i]\n",
    "    zhibiao_2017['1117'+i]=df2017[df2017['date']=='2017/11/17'][i]\n",
    "    zhibiao_2017['1125'+i]=df2017[df2017['date']=='2017/11/25'][i]\n",
    "    zhibiao_2017['1203'+i]=df2017[df2017['date']=='2017/12/03'][i]\n",
    "    zhibiao_2017['1211'+i]=df2017[df2017['date']=='2017/12/11'][i]\n",
    "    zhibiao_2017['1219'+i]=df2017[df2017['date']=='2017/12/19'][i]\n",
    "    zhibiao_2017['1227'+i]=df2017[df2017['date']=='2017/12/27'][i]\n",
    "    zhibiao_2017['0101'+i]=df2017[df2017['date']=='2018/01/01'][i]\n",
    "    zhibiao_2017['0109'+i]=df2017[df2017['date']=='2018/01/09'][i]\n",
    "    zhibiao_2017['0117'+i]=df2017[df2017['date']=='2018/01/17'][i]\n",
    "    zhibiao_2017['0125'+i]=df2017[df2017['date']=='2018/01/25'][i]\n",
    "    zhibiao_2017['0202'+i]=df2017[df2017['date']=='2018/02/02'][i]   \n",
    "    zhibiao_2017['0210'+i]=df2017[df2017['date']=='2018/02/10'][i]\n",
    "    zhibiao_2017['0218'+i]=df2017[df2017['date']=='2018/02/18'][i]\n",
    "    zhibiao_2017['0226'+i]=df2017[df2017['date']=='2018/02/26'][i]\n",
    "    zhibiao_2017['0306'+i]=df2017[df2017['date']=='2018/03/06'][i]\n",
    "    zhibiao_2017['0314'+i]=df2017[df2017['date']=='2018/03/14'][i]\n",
    "    zhibiao_2017['0322'+i]=df2017[df2017['date']=='2018/03/22'][i]\n",
    "    zhibiao_2017['0330'+i]=df2017[df2017['date']=='2018/03/30'][i]\n",
    "    zhibiao_2017['0407'+i]=df2017[df2017['date']=='2018/04/07'][i]\n",
    "    zhibiao_2017['0415'+i]=df2017[df2017['date']=='2018/04/15'][i]\n",
    "    zhibiao_2017['0423'+i]=df2017[df2017['date']=='2018/04/23'][i]\n",
    "    zhibiao_2017['0501'+i]=df2017[df2017['date']=='2018/05/01'][i]\n",
    "    zhibiao_2017['0509'+i]=df2017[df2017['date']=='2018/05/09'][i]\n",
    "    zhibiao_2017['0517'+i]=df2017[df2017['date']=='2018/05/17'][i]\n",
    "    zhibiao_2017['0525'+i]=df2017[df2017['date']=='2018/05/25'][i]\n",
    "    zhibiao_2017['0602'+i]=df2017[df2017['date']=='2018/06/02'][i]\n",
    "zhibiao_2017.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9e22c54e-85c6-46c9-8de4-69bd86e1848d",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2017.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "454e50dd-e796-424b-8280-df8e89dc6588",
   "metadata": {},
   "source": [
    "### 2016年的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "19fc7e3d-c650-4062-8bf0-f7b8bdef546d",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(102, 1209)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2016=df5.set_index('NAME') # 设置索引列\n",
    "zhibiao_2016= pd.DataFrame()\n",
    "zhibiao=list(df2016.columns)\n",
    "zhibiao.remove('date')\n",
    "for i in zhibiao:\n",
    "    zhibiao_2016['1007'+i]=df2016[df2016['date']=='2016/10/7'][i]\n",
    "    zhibiao_2016['1015'+i]=df2016[df2016['date']=='2016/10/15'][i]\n",
    "    zhibiao_2016['1023'+i]=df2016[df2016['date']=='2016/10/23'][i]\n",
    "    zhibiao_2016['1031'+i]=df2016[df2016['date']=='2016/10/31'][i]\n",
    "    zhibiao_2016['1108'+i]=df2016[df2016['date']=='2016/11/08'][i]\n",
    "    zhibiao_2016['1116'+i]=df2016[df2016['date']=='2016/11/16'][i]\n",
    "    zhibiao_2016['1124'+i]=df2016[df2016['date']=='2016/11/24'][i]\n",
    "    zhibiao_2016['1202'+i]=df2016[df2016['date']=='2016/12/02'][i]\n",
    "    zhibiao_2016['1210'+i]=df2016[df2016['date']=='2016/12/10'][i]\n",
    "    zhibiao_2016['1218'+i]=df2016[df2016['date']=='2016/12/18'][i]\n",
    "    zhibiao_2016['1226'+i]=df2016[df2016['date']=='2016/12/26'][i]\n",
    "    zhibiao_2016['0101'+i]=df2016[df2016['date']=='2017/01/01'][i]\n",
    "    zhibiao_2016['0109'+i]=df2016[df2016['date']=='2017/01/09'][i]\n",
    "    zhibiao_2016['0117'+i]=df2016[df2016['date']=='2017/01/17'][i]\n",
    "    zhibiao_2016['0125'+i]=df2016[df2016['date']=='2017/01/25'][i]\n",
    "    zhibiao_2016['0202'+i]=df2016[df2016['date']=='2017/02/02'][i]   \n",
    "    zhibiao_2016['0210'+i]=df2016[df2016['date']=='2017/02/10'][i]\n",
    "    zhibiao_2016['0218'+i]=df2016[df2016['date']=='2017/02/18'][i]\n",
    "    zhibiao_2016['0226'+i]=df2016[df2016['date']=='2017/02/26'][i]\n",
    "    zhibiao_2016['0306'+i]=df2016[df2016['date']=='2017/03/06'][i]\n",
    "    zhibiao_2016['0314'+i]=df2016[df2016['date']=='2017/03/14'][i]\n",
    "    zhibiao_2016['0322'+i]=df2016[df2016['date']=='2017/03/22'][i]\n",
    "    zhibiao_2016['0330'+i]=df2016[df2016['date']=='2017/03/30'][i]\n",
    "    zhibiao_2016['0407'+i]=df2016[df2016['date']=='2017/04/07'][i]\n",
    "    zhibiao_2016['0415'+i]=df2016[df2016['date']=='2017/04/15'][i]\n",
    "    zhibiao_2016['0423'+i]=df2016[df2016['date']=='2017/04/23'][i]\n",
    "    zhibiao_2016['0501'+i]=df2016[df2016['date']=='2017/05/01'][i]\n",
    "    zhibiao_2016['0509'+i]=df2016[df2016['date']=='2017/05/09'][i]\n",
    "    zhibiao_2016['0517'+i]=df2016[df2016['date']=='2017/05/17'][i]\n",
    "    zhibiao_2016['0525'+i]=df2016[df2016['date']=='2017/05/25'][i]\n",
    "    zhibiao_2016['0602'+i]=df2016[df2016['date']=='2017/06/02'][i]\n",
    "zhibiao_2016.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "46b69013-3d15-4c4f-b66e-1eaa1d85bdfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "zhibiao_2016.fillna(0, inplace=True) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "ab4fe337-9d9d-4855-9ce1-c9e5e09a3d71",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "old_dict = {'安阳县':410522,'宝丰县':410421,'泌阳县':411726,'博爱县':410822,'长葛市':411082,'长垣市':410783,'郸城县':411625,'登封市':410185,'邓州市':411381,'方城县':411322,\n",
    "'范县':410926,'封丘县':410727,'扶沟县':411621,'巩义市':410181,'光山县':411522,'固始县':411525,'淮滨县':411527,'潢川县':411526,'滑县':410526,'辉县市':410782,\n",
    "'获嘉县':410724,'郏县':410425,'浚县':410621,'兰考县':410225,'灵宝市':411282,'临颍县':411122,'林州市':410581,'栾川县':410324,'洛宁县':410328,'罗山县':411521,\n",
    "'鲁山县':410423,'卢氏县':411224,'鹿邑县':411628,'孟州市':410883,'渑池县':411221,'民权县':411421,'南乐县':410923,'南召县':411321,'内黄县':410527,'内乡县':411325,\n",
    "'宁陵县':411423,'平舆县':411723,'濮阳县':410928,'清丰县':410922,'沁阳市':410882,'淇县':410622,'杞县':410221,'确山县':411725,'汝南县':411727,'汝阳县':410326,\n",
    "'汝州市':410482,'上蔡县':411722,'商城县':411524,'商水县':411623,'沈丘县':411624,'社旗县':411327,'嵩县':410325,'遂平县':411728,'睢县':411422,'太康县':411627,\n",
    "'台前县':410927,'唐河县':411328,'汤阴县':410523,'桐柏县':411330,'通许县':410222,'卫辉市':410781,'尉氏县':410223,'温县':410825,'舞钢市':410481,'舞阳县':411121,\n",
    "'武陟县':410823,'项城市':411681,'襄城县':411025,'夏邑县':411426,'淅川县':411326,'西华县':411622,'新安县':410323,'新蔡县':411729,'荥阳市':410182,'新密市':410183,\n",
    "'新县':411523,'新乡县':410721,'新野县':411329,'新郑市':410184,'西平县':411721,'修武县':410821,'息县':411528,'西峡县':411323,'延津县':410726,'鄢陵县':411024,\n",
    "'叶县':410422,'伊川县':410329,'义马市':411281,'宜阳县':410327,'永城市':411481,'原阳县':410725,'虞城县':411425,'禹州市':411081,'柘城县':411424,'正阳县':411724,\n",
    "'镇平县':411324,'中牟县':410122}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8b0bd1e4-c751-486c-a11c-659c4a202f63",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_muchan['NAME'] = df_muchan['县名'].map(old_dict)\n",
    "# 将'NAME'列移到第一列位置\n",
    "cols = ['NAME'] + [col for col in df_muchan if col != 'NAME']\n",
    "df_muchan = df_muchan[cols]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "2732da9b-fb56-452b-ad45-36343bc1d81f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_2016 = zhibiao_2016.merge(df_muchan[['NAME', '2016年小麦亩产']], on='NAME', how='left')\n",
    "df_2017 = zhibiao_2017.merge(df_muchan[['NAME', '2017年小麦亩产']], on='NAME', how='left')\n",
    "df_2018 = zhibiao_2018.merge(df_muchan[['NAME', '2018年小麦亩产']], on='NAME', how='left')\n",
    "df_2019 = zhibiao_2019.merge(df_muchan[['NAME', '2019年小麦亩产']], on='NAME', how='left')\n",
    "df_2020 = zhibiao_2020.merge(df_muchan[['NAME', '2020年小麦亩产']], on='NAME', how='left')\n",
    "df_2021 = zhibiao_2021.merge(df_muchan[['NAME', '2021年小麦亩产']], on='NAME', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "69d1909b-dc1c-41fe-ae18-77a19cc1f73b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_2016.to_excel(r'数据\\16指标1.xlsx')\n",
    "df_2017.to_excel(r'数据\\17指标1.xlsx')\n",
    "df_2018.to_excel(r'数据\\18指标1.xlsx')\n",
    "df_2019.to_excel(r'数据\\19指标1.xlsx')\n",
    "df_2020.to_excel(r'数据\\20指标1.xlsx')\n",
    "df_2021.to_excel(r'数据\\21指标1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d4ccc441-a166-4897-8168-f12869da8f4c",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aa6b1f5c-b5f5-4818-83d0-9b1e154604ba",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
